﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using OffInvoiceExcelTemplate;
using System.Globalization;
using Microsoft.Office.Interop.Excel;
using System.Drawing;
using System.Xml;

namespace OffInvoiceExcelTemplate
{
    public partial class Hoja1
    {
        #region Fields

       public CultureInfo culture;
       public NumberFormatInfo num;
        FieldFormats ff;
        Calculos calculos;
        Excel.Workbook wb;
        Excel.Worksheet ws;
        string messagePath;
        Excel.Worksheet ws2;
        Hoja2 h2;
        Hoja3 h3;
        
       
        #endregion

        #region Event Handlers
              
       

        private void Hoja1_Startup(object sender, System.EventArgs e)
        {
            Excel.Worksheet ws = this.Application.Worksheets[1];
            
            culture = new CultureInfo(Application.LanguageSettings.get_LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI));
            num = culture.NumberFormat;
            ff = new FieldFormats(culture, num, culture.Name);
            messagePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Microsoft OffInvoice\Messages\" + culture.Name + ".xml";
            calculos = new Calculos(ff);
            wb = (Excel.Workbook)Application.ActiveWorkbook;
            ws = (Excel.Worksheet)wb.ActiveSheet;
            ws2 = wb.Sheets[3];
            h2 = Globals.Hoja2;
            h3 = Globals.Hoja3;
            pickDates();
           
         


        }

        private void Hoja1_Shutdown(object sender, System.EventArgs e)
        {

        }

        private void btnAddDetail_Click(object sender, EventArgs e)
        {
            try
            {                
                h2.edit(wb, false);
                calculos = new Calculos(ff);
                calculateDetailLine();
                addRow("detailLastRow");
                calculos.computeDetails(loadDetails());
                updateDiscounts();
                updateTotals();
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void btnDeleteDetail_Click(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb,false);
                int minRow = d_detailHeader.Row;
                int maxRow = detailLastRow.Row;
                int rowCount = t_details.Rows.Count;
                deleteRow(minRow, maxRow, rowCount, "rowNotBelongToDetailsRange");
                h2.edit(wb, true);
            }
            catch (Exception) { }

        }

        private void btnAddDiscount_Click(object sender, EventArgs e)
        {
            try
            {
                if (t_details.Rows.Count > 3)
                {
                    h2.edit(wb, false);
                    calculateDiscountLine();
                    addRow("discountLastRow");
                    calculos.computeDiscount(loadDiscounts());
                    updateTotals();
                    h2.edit(wb, true);
                }
            }
            catch (Exception) { }
        }

        private void btnUpdateDiscount_Click(object sender, EventArgs e)
        {
            h2.edit(wb, false);
            updateDiscounts();
            updateTotals();
            h2.edit(wb, true);

        }

        private void btnUpdateDetail_Click(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb, false);
                updateDetails();
                updateDiscounts();
                updateTotals();
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void btnDeleteDiscount_Click(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb, false);
                int minRow = d_discHeader.Row;
                int maxRow = discountLastRow.Row;
                int rowCount = t_discounts.Rows.Count;
                deleteRow(minRow, maxRow, rowCount, "rowNotBelongToDiscountsRange");
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void dtp_inv_issd_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb, false);
                Excel.Worksheet ws2 = wb.Sheets[3];
                Excel.Range rg = ws2.get_Range("InvIssue_IssueDate", missing);               
                string date = dtp_inv_issd.Text;
                rg.Value2 = date;
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void dtp_c_taxpsd_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb, false);
                Excel.Range rg = ws2.get_Range("CORR_TaxPeriod_StartDate", missing);
                string date = dtp_c_taxpsd.Text;
                rg.Value2 = date;
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void dtp_c_taxped_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                h2.edit(wb, false);
                Excel.Range rg = ws2.get_Range("CORR_TaxPeriod_EndDate", missing);
                string date = dtp_c_taxped.Text;
                rg.Value2 = date;
                h2.edit(wb,true);
            }
            catch (Exception) { }
        }

        #endregion

        #region Private Methods


       


        /// <summary>
        /// agrega una fila al final del rango especificado
        /// </summary>
        /// <param name="tableRange"></param>
        private void addRow(string tableRange)
        {
          
            try
            {
                if (wb == null)
                {
                    wb = (Excel.Workbook)this.Application.ActiveWorkbook;
                }
                if (ws == null)
                {
                    ws = wb.ActiveSheet;
                }

               
                Excel.Name rngN = wb.Names.Item(tableRange, missing, missing);
                Excel.Range rng = rngN.RefersToRange;
                rng.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);

                string ini = "A" + (rng.Row - 1);
                string fin = "AH" + (rng.Row - 1);
             
                Excel.Range rng2 = (Excel.Range)ws.get_Range(ini, fin);
                rng.Copy();
                rng2.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                rng.Select();
                rng.Copy();
                rng2.Select();
                ws.Paste();
                rng.ClearContents();
                //rng.Select();
                d_ud.Value2 = "0";
                d_price.Value2 = "0";
                d_iva.Value2 = "16";
                d_irpf.Value2 = "0";
                d_disc.Value2 = "0";
                d_taxBase.Value2 = "0";
                d_amount.Value2 = "0";

                if (tableRange.Equals("detailLastRow"))
                {
                    //string date = dateTimePicker2.Value.ToString("dd/MM/yyyy");
                    //ws.get_Range(ini, missing).Value2 = date;
                   
                    try
                    {
                        Excel.Range rg = ws.get_Range(ini, missing);
                        string date = dateTimePicker2.Value.ToString("d", culture);
                        DateTime dr2 = DateTime.Parse(date, culture);
                        string cf = culture.DateTimeFormat.ShortDatePattern;
                        dateTimePicker2.Format = DateTimePickerFormat.Custom;
                        dateTimePicker2.CustomFormat = cf;
                        dateTimePicker2.Value = dr2;
                        rg.Value2 = date;
                    }
                    catch (Exception ex) { }
                }

               
            }
            catch (Exception) { }
        }

        /// <summary>
        /// realiza los calculos de linea, al agregar detalles
        /// </summary>
        private void calculateDetailLine()
        {
            
            string concept = string.Empty;
            double und=-1;
            double price=-1;
            double iva=-1;
            double discount=0;
            double irpf=0;
            try
            {
               
                concept = d_description.Value2.ToString();
                und = double.Parse(d_ud.Value2.ToString(),num);
                price = double.Parse(d_price.Value2.ToString(),num);
                iva = double.Parse(d_iva.Value2.ToString(),num);
            }
            catch (Exception) { }

            try
            {
                if (d_disc.Value2 != null)
                {
                    discount = double.Parse(ff.discount_Validating(d_disc.Value2.ToString()),num);
                }
                
            }
            catch (Exception ex) {

                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDetails"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

                throw ex;
            }

            try
            {
                irpf = double.Parse(d_irpf.Value2.ToString(),num);
            }
            catch (Exception) { }
            try
            {
                ff.description_Validating(concept);
                ff.ud_Validating(und.ToString());
                ff.price_Validating(price.ToString());
                ff.iva_Validating(iva.ToString());
                ff.discount_Validating(discount.ToString());
                ff.irpf_Validating(irpf.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDetails"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                
                throw ex;

            }


            double costo = und * price;
            double baseimp = costo - (costo * discount / 100);
            double amount = baseimp + (baseimp * iva / 100) - (baseimp * irpf / 100);
           
            d_ud.Value2 = und;
            d_price.Value2 = price;
            d_disc.Value2 = discount;
            d_iva.Value2 = iva;
            d_taxBase.Value2 = string.Format(num, "{0:#,0.00}", baseimp);
            d_amount.Value2 = string.Format(num, "{0:#,0.00}", amount);
        }  
        
        /// <summary>
        /// 
        /// </summary>
        private void calculateDiscountLine()
        {
            double grossAmount=0;
            string description = string.Empty;
            string discRate = string.Empty;
            double discamoun = 0;
            

            try
            {
                 grossAmount = calculos.totalGrossAmount;
                 description = d_discConcept.Value2.ToString();
                 discRate = d_discRate.Value2.ToString();
            }
            catch (Exception) { }

            try
            {
                ff.description_Validating(description);
                ff.generalDiscount_Validating(discRate.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDiscounts"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                throw ex;

            }

             discamoun = grossAmount * double.Parse(discRate,num) / 100;
             d_discAmount.Value2 = string.Format(num, "{0:#,0.00}", discamoun);


        }
   
            
        /// <summary>
        /// cargar en una matriz de objetos, las lineas de la tabla de detalle
        /// </summary>
        /// <returns></returns>
        private object[,] loadDetails()
        {
            if (wb == null)
            {
                wb = this.Application.ActiveWorkbook;
            }

            if (ws == null)
            {
                ws = wb.Sheets[1];
            }

            

            int fecha = ((Excel.Range)ws.get_Range("Fecha", missing)).Column;
            int concepto = ((Excel.Range)ws.get_Range("Concepto", missing)).Column;
            int unidades = ((Excel.Range)ws.get_Range("Uds", missing)).Column;
            int precio = ((Excel.Range)ws.get_Range("Precio", missing)).Column;
            int descuento = ((Excel.Range)ws.get_Range("Descuento", missing)).Column;
            int iva = ((Excel.Range)ws.get_Range("IVA", missing)).Column;
            int irpf = ((Excel.Range)ws.get_Range("IRPF", missing)).Column;
            int baseImponible = ((Excel.Range)ws.get_Range("Base_Imponible", missing)).Column;
            int importe = ((Excel.Range)ws.get_Range("Importe", missing)).Column;
            Excel.Range tDetalles = (Excel.Range)ws.get_Range("t_details", missing);
            int rowcount = tDetalles.Rows.Count;

            object[,] details = new object[rowcount-3, 7];

            for (int i = 3; i < rowcount; i++)
            {
                details[i - 3, 0] = tDetalles.Cells[i, fecha].Text;
                details[i - 3, 1] = tDetalles.Cells[i, concepto].Text;
                details[i - 3, 2] = tDetalles.Cells[i, unidades].Text;
                details[i - 3, 3] = tDetalles.Cells[i, precio].Text;
                details[i - 3, 4] = tDetalles.Cells[i, descuento].Text;
                details[i - 3, 5] = tDetalles.Cells[i, iva].Text;
                details[i - 3, 6] = tDetalles.Cells[i, irpf].Text;
 
            }
            return details;

        }

        /// <summary>
        /// cargar en una matriz de objetos, las lineas de la tabla de descuento
        /// </summary>   
      
        private object[,] loadDiscounts()
        {
            if (wb == null)
            {
                wb = this.Application.ActiveWorkbook;
            }

            if (ws == null)
            {
                ws = wb.Sheets[1];
            }

            int disc_concepto = ((Excel.Range)ws.get_Range("disc_Concepto", missing)).Column;
            int disc_rate = ((Excel.Range)ws.get_Range("disc_rate", missing)).Column;
           
            Excel.Range tDiscount = (Excel.Range)ws.get_Range("t_discounts", missing);
            int rowcount = tDiscount.Rows.Count;

            object[,] discounts = new object[rowcount - 3, 2];

            for (int i = 3; i < rowcount; i++)
            {
                discounts[i - 3, 0] = tDiscount.Cells[i, disc_concepto].Text;
                discounts[i - 3, 1] = tDiscount.Cells[i, disc_rate].Text;
                           
            }
            return discounts;

        }

        /// <summary>
        /// establece los valores para la tabla de totales
        /// </summary>
        private void updateTotals()
        {
           
            resetTotalTable();

            grossamountLine.Value2 = string.Format(num, "{0:#,0.00}", calculos.totalGrossAmount);
            gdiscountLine.Value2 = string.Format(num, "{0:#,0.00}", calculos.totalGeneralDiscount);
            try
            {
                foreach (Taxes tax in calculos.taxes.TaxesList)
                {
                    string taxAmountprefix = string.Concat(Loader.LoadLabel(messagePath, "Infotaxamount"));
                    string baseImponiblePrefix = string.Concat(Loader.LoadLabel(messagePath, "InfoTaxablebaxe"));
                    taxAmountprefix += tax.AmmountPrefix + ")";
                    baseImponiblePrefix += tax.BaseImponiblePrefix + ")";
                    addTaxRow(baseImponiblePrefix, tax.Taxablebase);
                    addTaxRow(taxAmountprefix, tax.Taxamount);
                }
            }
            catch (Exception) { }
            IrpfValue.Value2 = string.Format(num, "{0:#,0.00}", calculos.totalTaxesWithHeld);
            totalAmountLine.Value2 = string.Format(num, "{0:#,0.00}", calculos.totalAmount);
           
        }

        /// <summary>
        /// agrega las filas con la informacion del iva
        /// </summary>
        /// <param name="label">leyenda de la columna del iva, ya sea baseimponible o cuota</param>
        /// <param name="data">valor de la columna</param>
        private void addTaxRow(string label, double data)
        {
         
            Excel.Name rngN = wb.Names.Item("irpfLine", missing, missing);
            Excel.Range rng = rngN.RefersToRange;

            int name = ((Excel.Range)ws.get_Range("IrpfName", missing)).Column;
            int value = ((Excel.Range)ws.get_Range("IrpfValue", missing)).Column;
            rng.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);

            string ini = "A" + (rng.Row - 1);
            string fin = "AH" + (rng.Row - 1);

            Excel.Range rng2 = (Excel.Range)ws.get_Range(ini, fin);
            rng.Copy();
            rng2.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
            ((Excel.Range)rng2.Cells[1, name]).Value2 = label;
            ((Excel.Range)rng2.Cells[1, value]).Value2 = data;
            rng.ClearOutline();
           


        }

        /// <summary>
        /// elimina las filas correspondientes al IVA
        /// </summary>
        private void resetTaxRow()
        {
           
            Excel.Name rngN = wb.Names.Item("t_totals", missing, missing);
            Excel.Range rng = rngN.RefersToRange;
            
            try
            {
                if (rng.Rows.Count > 5)
                {
                   
                    string ini = "A" + (discountLine.Row + 1);
                    string fin = "AH" + (irpfLine.Row - 1);
                    Excel.Range rng2 = (Excel.Range)ws.get_Range(ini, fin);
                    rng2.Delete();
                   
                }
            }
            catch (Exception) { }

         
        }

        /// <summary>
        /// restablece la tabla de totales
        /// </summary>
        private void resetTotalTable()
        {          
            resetTaxRow();
            totalvalues.ClearContents();           
        }

        private void updateDetails()
        {
            try
            {

                if (t_details.Rows.Count > 3)
                {

                    for (int i = 3; i < t_details.Rows.Count; i++)
                    {
                       
                        double ud = double.Parse(ff.ud_Validating(t_details.Cells[i, Uds.Column].Text),num);
                        double precio = double.Parse(ff.price_Validating(t_details.Cells[i, Precio.Column].Text),num);
                        double iva = double.Parse(ff.iva_Validating(t_details.Cells[i, IVA.Column].Text),num);
                        double desc=0;
                        double irpf=0;

                        if (t_details.Cells[i, Descuento.Column].Value2 != null)
                        {
                            desc = double.Parse(ff.discount_Validating(t_details.Cells[i, Descuento.Column].Text));
                        }
                       
                        if (t_details.Cells[i, IRPF.Column].Value2 != null)
                        {
                            irpf = double.Parse(ff.irpf_Validating(t_details.Cells[i, IRPF.Column].Text));
                        }

                        double baseImp = (ud * precio) - ((ud * precio) * desc / 100);
                        double amount = baseImp + (baseImp * iva / 100) - (baseImp * irpf / 100);
                        t_details.Cells[i, Base_Imponible.Column].Value2 = baseImp;
                        t_details.Cells[i, Importe.Column].Value2 = amount;
                    }

                    calculos = new Calculos(ff);
                    calculos.computeDetails(loadDetails());

                }
                else
                {
                    calculos = new Calculos(ff);
                    //totalvalues.Clear();
                }
            }
            catch (Exception ex) {

                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDiscounts"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                throw ex;
            }
        }

        private void updateDiscounts()
        {
            try
            {
                if (t_details.Rows.Count > 3)
                {

                    int cRate = disc_rate.Column;
                    int cAmount = disc_amount.Column;
                    int ini = d_discHeader.Row + 1;
                    double rate;
                    double dAmount;

                    for (int i = 3; i < t_discounts.Rows.Count; i++)
                    {

                        rate = double.Parse(t_discounts.Cells[i, cRate].Text);
                        dAmount = calculos.totalGrossAmount * rate / 100;
                        t_discounts.Cells[i, cAmount].Value2 = dAmount;

                    }

                    calculos.resetDiscounts();
                    calculos.computeDiscount(loadDiscounts());

                }
                else
                {
                    calculos = new Calculos(ff);
                    //totalvalues.Clear();
                }
            }
            catch (Exception) { }
        }     

        private void pickDates()
        {

            h2.edit(wb, false);
            try
            {
               Excel.Range rg = ws2.get_Range("InvIssue_IssueDate", missing);
               string date = dtp_inv_issd.Value.ToString("d",culture);
               DateTime dr2 = DateTime.Parse(date,culture);
               string cf = culture.DateTimeFormat.ShortDatePattern;
               dtp_inv_issd.Format = DateTimePickerFormat.Custom;
               dtp_inv_issd.CustomFormat = cf;
               dtp_inv_issd.Value = dr2;
               rg.Value2 = date;
            }
            catch (Exception ex) { }

            try
            {
                Excel.Range rg = ws2.get_Range("CORR_TaxPeriod_StartDate", missing);
                string date = dtp_c_taxpsd.Value.ToString("d", culture);
                DateTime dr2 = DateTime.Parse(date, culture);
                string cf = culture.DateTimeFormat.ShortDatePattern;
                dtp_c_taxpsd.Format = DateTimePickerFormat.Custom;
                dtp_c_taxpsd.CustomFormat = cf;
                dtp_c_taxpsd.Value = dr2;
                rg.Value2 = date;
            }
            catch (Exception) { }

            try
            {
                Excel.Range rg = ws2.get_Range("CORR_TaxPeriod_EndDate", missing);
                string date = dtp_c_taxped.Value.ToString("d", culture);
                DateTime dr2 = DateTime.Parse(date, culture);
                string cf = culture.DateTimeFormat.ShortDatePattern;
                dtp_c_taxped.Format = DateTimePickerFormat.Custom;
                dtp_c_taxped.CustomFormat = cf;
                dtp_c_taxped.Value = dr2;
                rg.Value2 = date;
            }
            catch (Exception) { }


            try
            {
                string date = dateTimePicker2.Value.ToString("d", culture);
                DateTime dr2 = DateTime.Parse(date, culture);
                string cf = culture.DateTimeFormat.ShortDatePattern;
                dateTimePicker2.Format = DateTimePickerFormat.Custom;
                dateTimePicker2.CustomFormat = cf;
                dateTimePicker2.Value = dr2;
                
            }
            catch (Exception) { }

            h2.edit(wb, true);

            try
            {
                string configPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Microsoft OffInvoice\Configuration\Config.xml";
                string imagePath = LoadLabel(configPath, "CompanyImage");
                pbImage.Image = Image.FromFile(imagePath); 
                pbImage.SizeMode = PictureBoxSizeMode.StretchImage;
            }
            catch (Exception) { }
        }

        private void deleteRow(int minRow, int maxRow, int rowCount, string tableCaption)
        {

            if (rowCount > 3)
            {
                if (wb == null)
                {
                    wb = this.Application.ActiveWorkbook;
                }
                if (ws == null)
                {
                    ws = wb.ActiveSheet;
                }

            
                int selectedRow = this.Application.ActiveCell.EntireRow.Row;

                if (selectedRow > minRow && selectedRow < maxRow)
                {
                    this.Application.ActiveCell.EntireRow.Delete(XlDirection.xlUp);


                    if (t_details.Rows.Count == 3)
                    {
                        if (t_discounts.Rows.Count > 3)
                        {
                            string fi = "A" + (d_discHeader.Row + 1);
                            string ff = "AH" + (discountLastRow.Row - 1);
                            Excel.Range drows = ws.get_Range(fi, ff);
                            drows.Delete();
                        }
                    }

                    updateDetails();
                    updateDiscounts();
                    updateTotals();

            
                }
                else
                {
                    MessageBox.Show(Loader.LoadLabel(ff.messagePath, tableCaption), Loader.LoadLabel(ff.messagePath, "captionDetails"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                    throw new Exception(tableCaption);
                }
            }

        }

       

        #endregion

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.dtp_inv_issd.ValueChanged += new System.EventHandler(this.dtp_inv_issd_ValueChanged);
            this.dtp_c_taxpsd.ValueChanged += new System.EventHandler(this.dtp_c_taxpsd_ValueChanged);
            this.dtp_c_taxped.ValueChanged += new System.EventHandler(this.dtp_c_taxped_ValueChanged);
            this.btnAddDetail.Click += new System.EventHandler(this.btnAddDetail_Click);
            this.btnDeleteDetail.Click += new System.EventHandler(this.btnDeleteDetail_Click);
            this.btnUpdateDetail.Click += new System.EventHandler(this.btnUpdateDetail_Click);
            this.btnAddDiscount.Click += new System.EventHandler(this.btnAddDiscount_Click);
            this.btnDeleteDiscount.Click += new System.EventHandler(this.btnDeleteDiscount_Click);
            this.btnUpdateDiscount.Click += new System.EventHandler(this.btnUpdateDiscount_Click);
            this.d_ud.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_ud_Deselected);
            this.BP_TaxIdentificationNumber.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_TaxIdentificationNumber_Change);
            this.BP_TaxIdentificationNumber.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.BP_TaxIdentificationNumber_Deselected);
            this.SP_CorporateName.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_CorporateName_Change);
            this.BP_CorporateName.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_CorporateName_Change);
            this.SP_Name.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_Name_Change);
            this.BP_Name.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_Name_Change);
            this.SP_FirstSurname.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_FirstSurname_Change);
            this.BP_FirstSurname.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_FirstSurname_Change);
            this.BP_SecondSurname.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_SecondSurname_Change);
            this.SP_SecondSurname.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_SecondSurname_Change);
            this.SP_Address.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_Address_Change);
            this.BP_Address.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_Address_Change);
            this.SP_PostCode.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_PostCode_Change);
            this.BP_PostCode.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_PostCode_Change);
            this.SP_Province.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_Province_Change);
            this.BP_Province.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_Province_Change);
            this.SP_Town.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_Town_Change);
            this.BP_Town.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_Town_Change);
            this.SP_Telephone.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_Telephone_Change);
            this.BP_Telephone.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.BP_Telephone_Change);
            this.SP_email.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_email_Change);
            this.sp_cc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.sp_cc_Change);
            this.sp_cc.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.sp_cc_Deselected);
            this.bp_cc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.bp_cc_Change);
            this.bp_cc.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.bp_cc_Deselected);
            this.th_cc.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.th_cc_Deselected);
            this.sp_ptc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.sp_ptc_Change);
            this.bp_ptc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.bp_ptc_Change);
            this.sp_rtc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.sp_rtc_Change);
            this.bp_rtc.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.bp_rtc_Change);
            this.d_price.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_price_Deselected);
            this.d_disc.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_disc_Deselected);
            this.d_iva.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_iva_Deselected);
            this.d_irpf.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_irpf_Deselected);
            this.d_discRate.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.d_discRate_Deselected);
            this.SP_TaxIdentificationNumber.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.SP_TaxIdentificationNumber_Change);
            this.SP_TaxIdentificationNumber.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.SP_TaxIdentificationNumber_Deselected);
            this.TH_TaxIdentificationNumber.Deselected += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(this.TH_TaxIdentificationNumber_Deselected);
            this.Startup += new System.EventHandler(this.Hoja1_Startup);
            this.Shutdown += new System.EventHandler(this.Hoja1_Shutdown);

        }

        #endregion

        #region deselected events
        /// <summary>
        /// realiza la llamada al metodo de validacion de cif para la parte correspondiente
        /// </summary>
        /// <param name="cifvalidation"></param>
        /// <param name="country"></param>
        /// <param name="cif"></param>
        /// <param name="party">indica a cual de las partes pertenece el cif, 1 = emisor, 2 = receptor, 3 = tercero</param>
        private string validarCif(bool cifvalidation, string country, string cif, int party)
        {
            try
            {
                cif = Validations.validarCif(cifvalidation, country, cif);
            }
            catch (Exception ex)
            {
                string label = ex.Message;

                if (label.Equals("countrynotinlist"))
                {
                    switch (party)
                    {
                        case 1: sp_cc.Select(); break;
                        case 2: bp_cc.Select(); break;
                        case 3: th_cc.Select(); break;
                    }

                }
                else
                {
                    switch (party)
                    {
                        case 1:
                            label = "ErrorInvalidCIFSP";
                            SP_TaxIdentificationNumber.Select(); break;
                        case 2:
                            label = "ErrorInvalidCIFBP";
                            BP_TaxIdentificationNumber.Select(); break;
                        case 3:
                            label = "ErrorInvalidCIFTH";
                            TH_TaxIdentificationNumber.Select(); break;
                    }
                }

                throw new Exception(label);
            }

            return cif;
        }

        private void SP_TaxIdentificationNumber_Deselected(Range Target)
        {
            try
            {
                if (h3.SP_CountryCode.Value2 != null && SP_TaxIdentificationNumber.Value2 != null)
                {
                    string country = h3.SP_CountryCode.Value2.ToString().Trim();
                    string cif = SP_TaxIdentificationNumber.Value2.ToString().Trim();
                    SP_TaxIdentificationNumber.Value2 = validarCif(true, country, cif, 1);
                }

            }
            catch (Exception ex)
            {

                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
            }
        }

        private void BP_TaxIdentificationNumber_Deselected(Range Target)
        {
            try
            {
                if (h3.BP_CountryCode.Value2 != null && BP_TaxIdentificationNumber.Value2 != null)
                {
                    string country = h3.BP_CountryCode.Value2.ToString().Trim();
                    string cif = BP_TaxIdentificationNumber.Value2.ToString().Trim();

                    BP_TaxIdentificationNumber.Value2 = validarCif(true, country, cif, 2);
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

            }
        }

        private void TH_TaxIdentificationNumber_Deselected(Range Target)
        {
            try
            {

                if (h3.TH_CountryCode.Value2 != null && TH_TaxIdentificationNumber.Value2 != null)
                {
                    string country = h3.TH_CountryCode.Value2.ToString().Trim();
                    string cif = TH_TaxIdentificationNumber.Value2.ToString().Trim();
                    TH_TaxIdentificationNumber.Value2 = validarCif(true, country, cif, 3);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

            }
        }

        private void sp_cc_Deselected(Range Target)
        {
            try
            {
                if (h3.SP_CountryCode.Value2 != null && SP_TaxIdentificationNumber.Value2 != null)
                {
                    string country = h3.SP_CountryCode.Value2.ToString().Trim();
                    string cif = SP_TaxIdentificationNumber.Value2.ToString().Trim();

                    SP_TaxIdentificationNumber.Value2 = validarCif(false, country, cif, 1);
                }
                selectNext(SP_Province);
            }
            catch (Exception ex)
            {

                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

            }
        }

        private void bp_cc_Deselected(Range Target)
        {
            try
            {
                if (BP_TaxIdentificationNumber.Value2 != null && h3.BP_CountryCode.Value2 != null)
                {
                    string country = h3.BP_CountryCode.Value2.ToString().Trim();
                    string cif = BP_TaxIdentificationNumber.Value2.ToString().Trim();

                    BP_TaxIdentificationNumber.Value2 = validarCif(false, country, cif, 2);
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
            }
        }

        private void th_cc_Deselected(Range Target)
        {
            try
            {

                if (TH_TaxIdentificationNumber.Value2 != null && h3.TH_CountryCode.Value2 != null)
                {
                    string country = h3.TH_CountryCode.Value2.ToString().Trim();
                    string cif = TH_TaxIdentificationNumber.Value2.ToString().Trim();
                    TH_TaxIdentificationNumber.Value2 = validarCif(false, country, cif, 3);
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(Loader.LoadLabel(messagePath, ex.Message), "Factura Electrónica", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

            }

        }

        #endregion

        #region selectNext events

        private void selectNext(NamedRange nextRange)
        {
            nextRange.Select();
            
        }

        private void selectNext(NamedRange nextRangeF, NamedRange nextRangeJ, NamedRange persontype)
        {
            if (persontype.Value2.ToString().Equals("F"))
            {
                nextRangeF.Select();
            }
            else
            {
                nextRangeJ.Select();
            }
        }
              
        private void sp_ptc_Change(Range Target)
        {
            sp_rtc.Select();
        }

        private void sp_rtc_Change(Range Target)
        {
            SP_TaxIdentificationNumber.Select();
        }

        private void SP_TaxIdentificationNumber_Change(Range Target)
        {
            selectNext(SP_Name, SP_CorporateName, h3.SP_PersonTypeCode);
           
        }

        private void SP_Name_Change(Range Target)
        {
            SP_FirstSurname.Select();
        }

        private void SP_FirstSurname_Change(Range Target)
        {
            SP_SecondSurname.Select();
        }

        private void SP_SecondSurname_Change(Range Target)
        {
            SP_Address.Select();
        }

        private void SP_CorporateName_Change(Range Target)
        {
            SP_Address.Select();
        }

        private void SP_Address_Change(Range Target)
        {
            SP_PostCode.Select();
        }

        private void SP_PostCode_Change(Range Target)
        {
            sp_cc.Select();
        }

        private void sp_cc_Change(Range Target)
        {
            SP_Province.Select();
        }

        private void SP_Province_Change(Range Target)
        {
            SP_Town.Select();
        }

        private void SP_Town_Change(Range Target)
        {
            SP_Telephone.Select();
        }

        private void SP_Telephone_Change(Range Target)
        {
            SP_email.Select();
        }

        private void SP_email_Change(Range Target)
        {
            bp_ptc.Select();
        }

        private void bp_ptc_Change(Range Target)
        {
            bp_rtc.Select();
        }

        private void bp_rtc_Change(Range Target)
        {
            BP_TaxIdentificationNumber.Select();
        }

        private void BP_TaxIdentificationNumber_Change(Range Target)
        {
            selectNext(BP_Name, BP_CorporateName, h3.BP_PersonTypeCode);
        }

        private void BP_Name_Change(Range Target)
        {
            BP_FirstSurname.Select();
        }

        private void BP_FirstSurname_Change(Range Target)
        {
            BP_SecondSurname.Select();
        }

        private void BP_SecondSurname_Change(Range Target)
        {
            BP_Address.Select();
        }

        private void BP_CorporateName_Change(Range Target)
        {
            BP_Address.Select();
        }

        private void BP_Address_Change(Range Target)
        {
            BP_PostCode.Select();
        }

        private void BP_PostCode_Change(Range Target)
        {
            bp_cc.Select();
        }

        private void bp_cc_Change(Range Target)
        {
            BP_Province.Select();
        }

        private void BP_Province_Change(Range Target)
        {
            BP_Town.Select();
        }

        private void BP_Town_Change(Range Target)
        {
            BP_Telephone.Select();
        }

        private void BP_Telephone_Change(Range Target)
        {
            BP_email.Select();
        }

        #endregion

        private string LoadLabel(string path, string tag)
        {
            string label = " ";
            try
            {
                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(path);
                string lb = xmldoc.GetElementsByTagName(tag).Item(0).InnerText;
                if (!string.IsNullOrEmpty(lb))
                    label = lb;

                return label;

            }
            catch (Exception)
            {
                return label;
            }
        }

        private void d_ud_Deselected(Range Target)
        {
            try
            {
                double xvar = double.Parse(d_ud.Value2.ToString(), num);
                d_ud.Value2 = string.Format(num, "{0:#,0.00}", xvar);
            }
            catch (Exception) {}
        }

        private void d_price_Deselected(Range Target)
        {
            try
            {
                double xvar = double.Parse(d_price.Value2.ToString(), num);
                d_price.Value2 = string.Format(num, "{0:#,0.00}", xvar);
            }
            catch (Exception) { }            
        }

        private void d_disc_Deselected(Range Target)
        {
            try
            {
                if (d_disc.Value2 != null)
                {                
                    d_disc.Value2 = ff.discount_Validating(d_disc.Value2.ToString());
                }
            }
            catch (Exception ex) 
            {
                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDetails"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
                          
            }
            
        }

        private void d_iva_Deselected(Range Target)
        {
            try
            {
                double xvar = double.Parse(d_iva.Value2.ToString(), num);
                d_iva.Value2 = string.Format(num, "{0:#,0.00}", xvar);
            }
            catch (Exception) { }           
        }

        private void d_irpf_Deselected(Range Target)
        {
            try
            {
                double xvar = double.Parse(d_irpf.Value2.ToString(), num);
                d_irpf.Value2 = string.Format(num, "{0:#,0.00}", xvar);
            }
            catch (Exception) { }           
        }

        private void d_discRate_Deselected(Range Target)
        {
            try
            {
                if (d_discRate.Value2 != null)
                {
                    d_discRate.Value2 = ff.discount_Validating(d_discRate.Value2.ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Loader.LoadLabel(ff.messagePath, ex.Message), Loader.LoadLabel(ff.messagePath, "captionDiscounts"), MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);

            }
        }

        
      
    }
}
